﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using Microsoft.VisualBasic.FileIO;
using System.Data.EntityClient;
using System.Data;
using System.Data.SqlClient;
using SSISFromCode.Helpers;

namespace SSISFromCode
{
    class Program
    {
        static void Main( string[] args )
        {
            Testit();

            //XMLSchema();
            //ShowPipelineComponentClassIds();

            Console.WriteLine( "done" );
            Console.ReadLine();
        }

        private static void Testit()
        {
            /// 
            /// Assuming we're using EF to work with this system.
            /// Entity connection string won't work here; this is OLEDB.  Can't get the SQL Server Destination data flow task to take anything different (UI won't allow either).
            /// When in use in an actual project, I passed in the EF string (only used one connection string) and just modified it to work with the OleDbDestination connection component
            /// by changing "Integrated Security=SSPI" to "Integrated Security=true" and adding in the provider.
            /// 
            /// The "WhateverClientEntities" model is a blank model just to hold an EF connection string.
            /// 
            var SSISConnectionStr = @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Whatever;Data Source=.\SQLEXPRESS";
            var EFConnectionStr = ( (EntityConnection)new WhateverClientEntities().Connection ).StoreConnection.ConnectionString;

            /// 
            /// Pick your options and set their props:
            /// 
            //var options = new DelimitedImportOptions() { Path = @"C:\temp\Eeemport\bubba.txt", ColumnNamesInFirstRow = true, Delimiter = DELIMITERS.COMMA.ToString(), Qualifier = MISC.QUOTE_QUALIFIER };
            var options = new XMLImportOptions() { Path = @"C:\temp\Eeemport\SampleXML\2011inquiryfile.XML" };
            //var options = new FixedWidthImportOptions() { Path = @"C:\temp\Eeemport\bubba.fixedwidth.txt", ColumnNamesInFirstRow = true, ColumnWidths = new List<int>( 2 ) { 10, 10 } };
            //var options = new ExcelImportOptions() { Path = @"C:\projects\noellevitz\OSDS\NoelLevitz.OSDS.Test\ImportFiles\bubba.xls", ColumnNamesInFirstRow = true, Worksheet = "Sheet1" };
            var importDirector = new ImportDirector( EFConnectionStr, options );

            /// 
            /// Set whether you want it to run.  Currently, if you set this to false, you will have to set the hard-coded location of the package output file in ImportDirector.Save (and comment out the assert).
            /// 
            var runnit = true;
            var ticky = Environment.TickCount;
            importDirector.ProcessImport( SSISConnectionStr, runnit );
            var duration = ( Environment.TickCount - ticky ) / 1000;
            importDirector.GeneratedMessages.ForEach( m => Console.WriteLine(m) );
            Console.WriteLine( "Duration: {0}", duration );
            if ( runnit )
            {
                CheckitOldSchool( importDirector.GeneratedTableName );
            }
            importDirector.ReleaseGeneratedTable();  // doesn't actually close in this project until the app shuts down; package stuff must still be holding on
        }

        static void CheckitOldSchool( string table )
        {
            var context = new WhateverClientEntities();
            var con = new SqlConnection( ((EntityConnection)context.Connection).StoreConnection.ConnectionString );
            var cmd = new SqlCommand( string.Format( "select * from {0}", table ), con );
            con.Open();
            using ( var reader = cmd.ExecuteReader( CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection) )
            {
                if ( reader.Read() )
                {
                    Console.WriteLine( "First val: {0}", reader.GetValue( 1 ) );
                    var count = 1;
                    while ( reader.Read() )
                    {
                        count++;
                    }
                    Console.WriteLine( "{0} rows", count );
                    Console.WriteLine( "{0} cols", reader.FieldCount );
                }
                else
                {
                    Console.WriteLine("you got no data");
                }
            }
        }

        static void XMLSchema()
        {
            new SchemaGenerator().Generate( @"C:\temp\Eeemport\bubba.xml", @"C:\temp\Eeemport\fromCode.xsd" );
        }

        static void ShowPipelineComponentClassIds()
        {
            foreach ( PipelineComponentInfo p in new Application().PipelineComponentInfos )
            {
                //File.AppendAllText( @"c:\temp\components.txt", string.Format( "{0};{1};{2}{3}", p.CreationName, p.Description, p.Name, Environment.NewLine ));
                Console.WriteLine( "{0};{1};{2}", p.CreationName, p.Description, p.Name );
            }
        }

    }
}
